package com.andrew.demo.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

import javax.sql.DataSource;

import com.andrew.demo.exception.UserTransactionException;
import com.andrew.demo.mapper.UserMapper;
import com.andrew.demo.model.User;
import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.namedparam.*;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;


@Repository
@Transactional
public class UserDao extends NamedParameterJdbcDaoSupport {

    @Autowired
    public UserDao(DataSource dataSource) {
        this.setDataSource(dataSource);
    }

    public List<User> getUser() {
        String sql = new UserMapper().TableInfoEntity.SELECT_SQL;
        UserMapper userMapper = new UserMapper();
        List<User> list = this.getJdbcTemplate().query(sql, userMapper);
//        User user = this.getUserById(1);

//        getUser2();
        //getUser3();

//        getUser3();
//        getUser4();
//        getUser5();
//        getUser6();
        return list;
    }

    public List<User> getUser3() {
        String sql = "select * from `User` where 1=1 and name=:name";
        SqlParameterSource namedParameters = new MapSqlParameterSource("name", 222);
        UserMapper userMapper = new UserMapper();
        List<User> list = super.getNamedParameterJdbcTemplate().query(sql, namedParameters, userMapper);
        return list;
    }

    public List<User> getUser4() {
        String sql = "select * from `User` where 1=1 and  name=:name";
        Map<String, String> namedParameters = Collections.singletonMap("name", "444");

        UserMapper userMapper = new UserMapper();
        List<User> list = super.getNamedParameterJdbcTemplate().query(sql, namedParameters, userMapper);
        return list;
    }

    public List<User> getUser5() {
        String sql = "select * from `User` where 1=1 and  name=:name";

        User user = new User();
        user.setName("555");
        SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(user);
        UserMapper userMapper = new UserMapper();
        List<User> list = super.getNamedParameterJdbcTemplate().query(sql, namedParameters, userMapper);
        return list;
    }


    public List<Map<String, Object>> getUser6() {
        String sql = "select * from `User` where 1=1";
        List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql);
        return list;
    }

    public List<User> getUser2() {
        String sql = "select * from `User` where 1=1 and name=:name";
//        String sql="select * from `User` where 1=1 and  IF(:name IS NULL,0=0, name=:name)";
        // String sql="select * from `User` where 1=1 and  (:name is null || name=:name)";
        UserMapper userMapper = new UserMapper();
        Map<String, Object> paramMap = new HashMap();
        paramMap.put("name", null);
        List<User> list = super.getNamedParameterJdbcTemplate().query(sql, paramMap, userMapper);
        return list;
    }

    public User getUserById(Integer id) {
        String sql = new UserMapper().TableInfoEntity.SELECT_SQL + " where id = ?";
        Object[] params = new Object[]{id};
        UserMapper userMapper = new UserMapper();
        User user = this.getJdbcTemplate().queryForObject(sql, params, userMapper);
        return user;
    }

    @Transactional(propagation = Propagation.MANDATORY)
    public void addAge(Integer id, int addnum) throws UserTransactionException {
        User user = this.getUserById(id);
        if (user == null) {
            throw new UserTransactionException("User not found" + id);
        }
        int newAge = user.getAge() + addnum;
        if (newAge < 0) {
            throw new UserTransactionException("The Age in the User" + id + "is illegal" + newAge);
        }
        user.setAge(newAge);

        String sql = "update User set age = ? where id = ?;";
        this.getJdbcTemplate().update(sql, user.getAge(), user.getId());
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = UserTransactionException.class)
    public void setAge(int id, int addnum) throws UserTransactionException {
        addAge(id, addnum);
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = UserTransactionException.class)
    public void insert() throws UserTransactionException {
        String sql = "INSERT INTO User ( name, sex, age) VALUES (:n, :s, :a)";
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("n", "zy");
        paramMap.put("s", "女");
        paramMap.put("a", 11);

        NamedParameterJdbcTemplate namedParameterJdbcTemplate =
                new NamedParameterJdbcTemplate(getDataSource());
        namedParameterJdbcTemplate.update(sql, paramMap);

        insert2();

    }

    @Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = UserTransactionException.class)
    public void batchUpdate() throws UserTransactionException {

        List<User> list = new ArrayList<>();
        User one = new User();
        one.setId(1);
        one.setName("1111");
        list.add(one);

        User two = new User();
        one.setId(2);
        two.setName("2222");
        list.add(two);

        User three = new User();
        three.setName("3333");
        one.setId(3);
        list.add(three);

        String sql = "update User set name= ? where id= ?";
        int[] arr = this.getJdbcTemplate().batchUpdate(
                sql,
                new BatchPreparedStatementSetter() {
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        ps.setString(1, list.get(i).getName());
                        ps.setInt(2, list.get(i).getId());
                    }

                    public int getBatchSize() {
                        return list.size();
                    }
                }
        );
        int[] aa = arr;

    }


    @Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = UserTransactionException.class)
    public void insert2() throws UserTransactionException {
        String sql = "INSERT INTO User ( name, sex, age) VALUES (:name, :sex, :age)";

        User user = new User();
        user.setName("As");
        user.setAge(33);
        user.setSex("男");

        SqlParameterSource paramSource = new BeanPropertySqlParameterSource(user);
        NamedParameterJdbcTemplate namedParameterJdbcTemplate =
                new NamedParameterJdbcTemplate(getDataSource());

        namedParameterJdbcTemplate.update(sql, paramSource);

    }

}